package com.eastcom.collection.tools.db

import oracle.jdbc.driver.OracleTypes

import org.springframework.dao.DataAccessException
import org.springframework.jdbc.core.CallableStatementCallback
import org.springframework.jdbc.core.CallableStatementCreator

import com.eastcom.collection.context.ServerContext;
import com.eastcom.collection.provider.*

import java.net.InetAddress.Cache;
import java.sql.CallableStatement
import java.sql.Connection
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException
import java.sql.Statement;
import java.sql.Timestamp
import java.sql.Types;
import java.text.SimpleDateFormat
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher
import java.util.regex.Pattern

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Component;



@Component("dbEngine")
@Scope("prototype")
public class DbEngine{
	@Resource(name = "jdbcUtil")
	private JdbcUtil jdbcUtil;
	private static Log logger = LogFactory.getLog(DbEngine.class);
	public DbEngine() {
		super();
		// TODO 自动生成的构造函数存根
	}
	//取得当前数据库的表信息
	public boolean getTableInfo(String dbId, String tableName) {
		if(ServerContext.dbMap.containsKey(dbId)){
			DbInfo dbInfo = ServerContext.dbMap.get(dbId);
			if(dbInfo.getTables().containsKey(tableName)){
				return true;
			}
		}
		// TODO 自动生成的方法存根
		String sql = "select * from "+tableName;
		logger.debug(sql);
		List<String> columnNames = new ArrayList<String>(10);
		List<Integer> columnTypes = new ArrayList<Integer>(10);
		List<String> primaryKeys = new ArrayList<String>(10);
		try {
			ResultSet result = jdbcUtil.queryPageAbsolute(sql,null,null, 1, 1);
			ResultSetMetaData metaData = result.getMetaData();
			int count = metaData.getColumnCount();
			for(int i = 1; i <= count; i++){
				columnNames.add(metaData.getColumnName(i).toUpperCase());
				System.out.println(metaData.getColumnType(i));
				//将date转成timestamp
				if(metaData.getColumnType(i) == Types.DATE){
					columnTypes.add(Types.TIMESTAMP);
				}else{
					columnTypes.add(metaData.getColumnType(i));
				}
				System.out.println(metaData.getColumnName(i)+metaData.getColumnClassName(i));
			}
			ResultSet rst = jdbcUtil.getDataSource().getConnection().getMetaData().getPrimaryKeys(null, null, tableName);
			while(rst.next()){
				primaryKeys.add(rst.getString("COLUMN_NAME"));
				System.out.println(rst.getString("TABLE_NAME") + "  " +
						rst.getString("COLUMN_NAME"));
			}
			//数据库信息
			//考虑数据库名key是否重复
			if(! ServerContext.dbMap.containsKey(dbId)){
				DbInfo dbInfo = new DbInfo();
				dbInfo.setName(dbId);
				Map<String, Table> tables = new HashMap<String, Table>();

				Table table = new Table();
				table.setFeildName(columnNames);
				table.setPrimaryKeys(primaryKeys);
				table.setName(tableName);
				table.setFeildType(columnTypes);
				tables.put(tableName, table);
				dbInfo.setTables(tables);
				ServerContext.dbMap.put(dbId, dbInfo);
			}else{
				Table table = new Table();
				table.setFeildName(columnNames);
				table.setPrimaryKeys(primaryKeys);
				table.setName(tableName);
				table.setFeildType(columnTypes);
				ServerContext.dbMap.get(dbId).getTables().put(tableName, table);
			}

		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public void batchUpdateSQL(String sql, List<Object[]> param, int[]types) {
		// TODO 自动生成的方法存根
		jdbcUtil.batchUpdate(sql, param, types);
	}

	public boolean executeQuerySQL(String sql) {
		// TODO 自动生成的方法存根
		SqlRowSet rowSet = jdbcUtil.queryForRowSet(sql);
		SqlRowSetMetaData setMetaData = rowSet.getMetaData();
		String[] tableNames = setMetaData.getColumnNames();
		int columnNumber = setMetaData.getColumnCount();
		for(String string: tableNames){
			System.out.println("info"+string);
		}
		return false;
	}

	public boolean executeUpdateSQL(String sql, Object[] param, int[]types) {
		// TODO 自动生成的方法存根
		int cnt = jdbcUtil.update(sql, param, types);
		System.out.println(cnt);
		return cnt > 0 ? true : false;
	}

	public List executeStoredProcedure(String sql, Object[] param,String[] outOrIn, int[] types,List<String> cursorKeys) {
		// TODO 自动生成的方法存根
		int[] tempTypes = types
		List resultList = (List) jdbcUtil.execute(
				new CallableStatementCreator() {

					public CallableStatement createCallableStatement(Connection con) throws SQLException {
						String storedProc = sql;// 调用的sql
						println storedProc
						//                        String
						CallableStatement cs = con.prepareCall(storedProc)
						println cs
						int i = 0
						param.each {
							println it
							println it instanceof Timestamp
							println outOrIn[i]
							println types[i]
							if(outOrIn[i].equalsIgnoreCase("IN")){
								println "iiiiiiiiiiiiii"
								if(types[i] == null || types[i] == 0){
									if(it instanceof String){
										cs.setString(i+1,it)
										tempTypes[i] = Types.VARCHAR
									}else if(it instanceof Integer){
										cs.setInt(i+1,it)
										tempTypes[i] = Types.INTEGER
									}else if(it instanceof Timestamp){
										println "dsdddddffyy"
										cs.setTimestamp(i+1,it)
										tempTypes[i] = Types.TIMESTAMP
									}else if(it instanceof java.sql.Date){
										cs.setDate(i+1,it)
										tempTypes[i] = Types.DATE
									}else if(it instanceof Double){
										cs.setDouble(i+1,it)
										tempTypes[i] = Types.DOUBLE
									}else if(it instanceof BigDecimal){
										cs.setBigDecimal(i+1,it)
										tempTypes[i] = Types.DECIMAL
									}else if(it instanceof Float){
										cs.setFloat(i+1,it)
										tempTypes[i] = Types.FLOAT
									}else if(types[i] == Types.OTHER){
										cs.setObject(i+1,it)
									}else{
										logger.error("暂时不支持类型！值="+it)
										throw SQLException
									}
								}else{
									if(types[i] == Types.VARCHAR || types[i] == Types.NVARCHAR){
										cs.setString(i+1,it)
									}else if(types[i] == Types.INTEGER){
										cs.setInt(i+1,it)
									}else if(types[i] == Types.TIMESTAMP){
										println "dfffffffooooooooooooooooo"
										cs.setTimestamp(i+1,it)
										println "dfffffffoooooooooodfffffffooooooo"
									}else if(types[i] == Types.DATE){
										cs.setDate(i+1,it)
									}else if(types[i] == Types.DOUBLE){
										cs.setDouble(i+1,it)
									}else if(types[i] == Types.DECIMAL){
										cs.setBigDecimal(i+1,it)
									}else if(types[i] == Types.OTHER){
										cs.setObject(i+1,it)
									}else if(types[i] == Types.FLOAT){
										cs.setFloat(i+1,it)
									}else{
										logger.error("暂时不支持类型！值="+it)
										throw SQLException
									}
								}

							}else if(outOrIn[i].equalsIgnoreCase("OUT")){
								//								cs.registerOutParameter(i, OracleTypes.CURSOR);// 注册输出参数的类型
								cs.registerOutParameter(i+1, types[i]);// 注册输出参数的类型
							}
							i++
						}
						println "dddddddddddddfa"
						//                        cs.setString(1, "p1");// 设置输入参数的值
						return cs;
					}
				}, new CallableStatementCallback() {

					public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
						List<Object> resultList = new ArrayList<Object>()
						cs.execute();
						println "dddddddddddddf"
						int i = 0
						int ix = 0
						for(String tp : outOrIn){
							if(tp.equalsIgnoreCase("OUT")){
								if(types[i] == Types.VARCHAR || types[i] == Types.NVARCHAR){
									resultList.add(cs.getString(i+1))
								}else if(types[i] == Types.INTEGER){
									resultList.add(cs.getInt(i+1))
								}else if(types[i] == Types.TIMESTAMP){
									resultList.add(cs.getTimestamp(i+1))
								}else if(types[i] == Types.DATE){
									resultList.add(cs.getDate(i+1))
								}else if(types[i] == Types.DOUBLE){
									resultList.add(cs.getDouble(i+1))
								}else if(types[i] == Types.DECIMAL){
									resultList.add(cs.getBigDecimal(i+1))
								}else if(types[i] == Types.FLOAT){
									resultList.add(cs.getFloat(i+1))
								}else if(types[i] == Types.FLOAT){
									resultList.add(cs.getObject(i+1))
								}else if(types[i] == OracleTypes.CURSOR){
									List resultsMap = new ArrayList();
									resultList.add(resultsMap)
									ResultSet rs = (ResultSet) cs.getObject(i+1);// 获取游标一行的值
									while (rs.next()) {// 转换每行的返回值到Map中
										Map rowMap = new HashMap();
										cursorKeys.each {
											rowMap.put(it, rs.getObject(it))
										}
										resultsMap.add(rowMap);
									}

									rs.close();
								}else{
									logger.error("暂时不支持类型！值="+types[i])
									throw SQLException
								}
							}
							i++
						}
						return resultList;
					}
				});
		return resultList
	}


	public static boolean loadDb(String dstDb, String dstTb,String dstFields,List<String> dstFieldList,List<Integer> dstFieldTypeList, boolean isDomain) {
		try {
			String db = dstDb.trim()
			String tb = dstTb.trim()
			println  "dstFields="+dstFields
			if (ServerContext.dbMap.containsKey(db)) {
				DbInfo dbInfo = ServerContext.dbMap.get(db)
				Map<String, Table> tables = dbInfo.tables
				if (tables.containsKey(db)) {
					println "已存在表[" + tb + "]信息。。。"
				} else {
					println "正在加载表[" + tb + "]信息。。。"
					DbContextProvider dbContextProvider = new DbContextProvider()
					if (!dbContextProvider.loadDbSource(db, tb,isDomain)) {
						println "加载表[" + tb + "]信息失败！"
						return false
					}
					println "查询表[" + tb + "]信息成功！"
				}
			} else {
				println "正在加载表[" + tb + "]信息。。。"
				DbContextProvider dbContextProvider = new DbContextProvider()
				if (!dbContextProvider.loadDbSource(db, tb,isDomain)) {
					println "加载表[" + db + "]信息失败！"
					return false
				}
				println "查询表[" + db + "]信息成功！"
			}
			if(dstFields.trim().equalsIgnoreCase("ALL")){
				logger.debug("目的数据库配置ALL，使用所有字段!")
				dstFieldList.addAll(ServerContext.dbMap.get(dstDb.trim()).tables.get(dstTb.trim()).feildName)
				logger.debug("dstFieldList.size="+dstFieldList.size())
			}else {
				String[] fieldArr  = dstFields.split("\\s*,\\s*")
				fieldArr.each { dstFieldList.add(it) }
			}
			if (!checkDstDbFeilds(db, tb, dstFieldList)) {
				println "配置表字段与实际或者缓存中不一致！请检查配置和数据库信息是否一致！"
				return false
			}
			dstFieldList.each {
				int index = ServerContext.dbMap.get(db).getTables().get(tb).feildName.indexOf(it.toUpperCase())
				dstFieldTypeList.add(ServerContext.dbMap.get(db).getTables().get(tb).feildType.get(index))
			}
		} catch (Exception e) {
			e.printStackTrace()
			logger.error(e.getMessage())
		}
		return true
	}

	public static boolean checkDstDbFeilds(String db, String tb, List<String> dstFieldList) {
		List<String> temp = new ArrayList()
		dstFieldList.each {
			temp.add(it.toUpperCase())
		}
		if (ServerContext.dbMap.get(db).tables.get(tb).feildName.containsAll(temp)) {
			return true
		}
		return false
	}

	public static String makeSql(List<String> fieldList, String table) {
		StringBuffer sqlBuffer = new StringBuffer()
		int i = 0
		sqlBuffer.append("insert into " + table + "(")
		fieldList.each {
			sqlBuffer.append(it)
			i++
			if (i < fieldList.size()) {
				sqlBuffer.append(",")
			}
		}
		sqlBuffer.append(") values (")
		for (int s = 0; s < fieldList.size(); s++) {
			sqlBuffer.append("?")
			if (s < fieldList.size() - 1) {
				sqlBuffer.append(",")
			}
		}
		sqlBuffer.append(")")
		return sqlBuffer.toString()
	}

	public static List<Object> fillInsertData(List<Object> rawDataList, List<Integer> dstSqlDataType,List<String> timeFormatList) {
		int i = 0
		int size = 0
		List<Object> dstDataList = new ArrayList<>()
		if(timeFormatList != null){
			size = timeFormatList.size()
			logger.debug("timeFormatSize="+size)
		}
		try {
			rawDataList.each {
				Object value = it
				//                println(value)
				Integer type = dstSqlDataType.get(i)
				i++
				println("type = "+type)
				switch (type) {
					case Types.TIMESTAMP:
					//目前多个regex只支持一种时间格式
						int id = 0;
						java.util.Date date = null;
						Timestamp time
						for(int j = 0; j < timeFormatList.size(); j++) {
							try {
								SimpleDateFormat simpleDateFormat = new SimpleDateFormat(timeFormatList.get(j));
								date = simpleDateFormat.parse(value)
								println(date)
								break;
							} catch (Exception e) {
								// TODO: handle exception
								println("时间转换错误！时间："+value+"格式："+timeFormatList.get(j))
								continue
							}
						}


						logger.debug(date.toLocaleString());
						SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						String string = simpleDateFormat2.format(date);
						logger.debug(string);
						Timestamp timestamp = Timestamp.valueOf(string);
						dstDataList.add(timestamp);
						break;
					case Types.VARCHAR:
					case Types.NVARCHAR:
						dstDataList.add(value)
						break;
					case Types.DECIMAL:
						dstDataList.add(BigDecimal.valueOf(Double.valueOf(value)))
						break;
					case Types.NUMERIC:
						dstDataList.add(BigDecimal.valueOf(Double.valueOf(value)))
						break;
					case Types.INTEGER:
						dstDataList.add(Integer.valueOf(value))
						break;
					case Types.DOUBLE:
					case Types.FLOAT:
						dstDataList.add(Double.valueOf(value))
						break
					case Types.DECIMAL:
					default:
						dstDataList.add(null)
						logger.warn("入库数据库字段无法识别！使用Null插入！")
						break;
				}
			}

		}catch (Exception e){
			e.printStackTrace()
		}
		return dstDataList
	}

	public static boolean doInsertResult(List<List<String>> parseResultList, String dstdbBean,String insertSql, int[] sqlTypes,String timeFormat){
		try{
			DbEngine dbEngine = SpringContextProvider.getBean("dbEngine")
			dbEngine.getJdbcUtil().setDataSource(DynamicBeanDomianProvider.getBean(dstdbBean))
			println parseResultList.size()
			for(List<String> raw: parseResultList){
				Object[] param = new Object[raw.size()]
				for(int i = 0; i < raw.size(); i++){
					print raw.get(i)+"--"+sqlTypes[i]+"\t"
					try {
						switch(sqlTypes[i]){
							case Types.INTEGER:
								param[i] = Integer.valueOf(raw.get(i))
								break
							case Types.FLOAT:
							case Types.DOUBLE:
								param[i] = Double.valueOf(raw.get(i))
								break
							case Types.DECIMAL:
							case Types.NUMERIC:
								param[i] = BigDecimal.valueOf(Double.valueOf(raw.get(i)))
								break
							case Types.TIMESTAMP:
								java.util.Date date = new SimpleDateFormat(timeFormat).parse(raw.get(i))
								logger.debug(date)
								param[i] = new Timestamp(date.getTime())
								break
							case Types.DATE:
								java.util.Date date = new SimpleDateFormat(timeFormat).parse(raw.get(i))
								logger.debug(date)
								param[i] = new java.sql.Date(date.getTime())
								break
							case Types.VARCHAR:
							case Types.NVARCHAR:
								param[i] = raw.get(i)
								break
							case Types.OTHER:
								param[i] = raw.get(i)
								break;
							default:
								param[i] = null
								logger.warn("入库数据库字段无法识别！使用Null插入！")
								break;
						}
					} catch (Exception e) {
						e.printStackTrace()
						logger.error(e.getMessage())
						param[i]=null
					}
				}
				try {
					dbEngine.getJdbcUtil().update(insertSql, param, sqlTypes)
				} catch (Exception e) {
					e.printStackTrace()
				}
				println ""
			}
			println "sdfdsfdsdone!"
		}catch (Exception e) {
			e.printStackTrace()
			logger.error(e.getMessage())
		}
	}

	public static boolean doBatchInsertResult(List<List<String>> parseResultList, String dstdbBean,String insertSql, int[] sqlTypes,String timeFormat){
		try{
			DbEngine dbEngine = SpringContextProvider.getBean("dbEngine")
			dbEngine.getJdbcUtil().setDataSource(DynamicBeanDomianProvider.getBean(dstdbBean))
			List<Object[]> paramList = new ArrayList()
			for(List<String> raw: parseResultList){
				Object[] param = new Object[raw.size()]
				for(int i = 0; i < raw.size(); i++){
					print raw.get(i)+"--"+sqlTypes[i]+"\t"
					try {
						switch(sqlTypes[i]){
							case Types.INTEGER:
								param[i] = Integer.valueOf(raw.get(i))
								break
							case Types.FLOAT:
							case Types.DOUBLE:
								param[i] = Double.valueOf(raw.get(i))
								break
							case Types.DECIMAL:
							case Types.NUMERIC:
								param[i] = BigDecimal.valueOf(Double.valueOf(raw.get(i)))
								break
							case Types.TIMESTAMP:
								java.util.Date date = new SimpleDateFormat(timeFormat).parse(raw.get(i))
								logger.debug(date)
								param[i] = new Timestamp(date.getTime())
								break
							case Types.DATE:
								java.util.Date date = new SimpleDateFormat(timeFormat).parse(raw.get(i))
								logger.debug(date)
								param[i] = new Date(date.getTime())
								break
							case Types.VARCHAR:
							case Types.NVARCHAR:
								param[i] = raw.get(i)
								break
							case Types.OTHER:
								param[i] = raw.get(i)
								break;
							default:
								param[i] = null
								logger.warn("入库数据库字段无法识别！使用Null插入！")
								break;
						}
					} catch (Exception e) {
						e.printStackTrace()
						logger.error(e.getMessage())
						param[i]=null
					}
				}
				paramList.add(param)
				println ""
			}
			dbEngine.getJdbcUtil().batchUpdate(insertSql, paramList, sqlTypes)
		}catch(Exception e){
			e.printStackTrace()
			logger.error(e.getMessage())
		}
	}

	public static boolean doInsertResult(String dstdbBean, String timeFormate,int[] sqlTypes,String insertSql,List<List<String>> parseResultList){
		try{
			DbEngine dbEngine = SpringContextProvider.getBean("dbEngine")
			dbEngine.getJdbcUtil().setDataSource(DynamicBeanDomianProvider.getBean(dstdbBean))
			for(List<String> raw: parseResultList){
				Object[] param = new Object[raw.size()]
				println raw.size()
				for(int i = 0; i < raw.size(); i++){
					print raw.get(i)+"--"+sqlTypes[i]+"\t"
					try {
						switch(sqlTypes[i]){
							case Types.INTEGER:
								param[i] = Integer.valueOf(raw.get(i))
								break
							case Types.FLOAT:
							case Types.DOUBLE:
								param[i] = Double.valueOf(raw.get(i))
								break
							case Types.DECIMAL:
							case Types.NUMERIC:
								param[i] = BigDecimal.valueOf(Double.valueOf(raw.get(i)))
								break
							case Types.TIMESTAMP:
								java.util.Date date = new SimpleDateFormat(timeFormate).parse(raw.get(i))
								logger.debug(date)
								param[i] = new Timestamp(date.getTime())
								break
							case Types.DATE:
								java.util.Date date = new SimpleDateFormat(timeFormate).parse(raw.get(i))
								logger.debug(date)
								param[i] = new Date(date.getTime())
								break
							case Types.VARCHAR:
							case Types.NVARCHAR:
								param[i] = raw.get(i)
								break
							case Types.OTHER:
								param[i] = raw.get(i)
								break;
							default:
								param[i] = null
								logger.warn("入库数据库字段无法识别！使用Null插入！")
								break;
						}
					} catch (Exception e) {
						e.printStackTrace()
						logger.error(e.getMessage())
						param[i]=null
					}
				}
				try{
					dbEngine.getJdbcUtil().update(insertSql, param, sqlTypes)
				}catch(Exception e){
					e.printStackTrace()
					logger.error(e.getMessage())
				}
				println ""
			}
			parseResultList.clear()
		}catch(Exception e){
			e.printStackTrace()
			logger.error(e.getMessage())
		}
	}

	public static String doDbInit(String dstdbBean,List<String> dstFieldList,List<Integer> dstFieldTypeList,String insertSql,boolean isDomain){
		try{
			String reg = "\\w+\\s+\\w+([^\\(\\),]+)\\s*\\(([^\\)]+)"
			String dstTb
			String dstFields
			Pattern pattern = Pattern.compile(reg)
			Matcher matcher = pattern.matcher(insertSql)
			if(matcher.find()){
				dstTb = matcher.group(1)
				dstFields = matcher.group(2)
			}
			loadDb(dstdbBean, dstTb, dstFields, dstFieldList, dstFieldTypeList, isDomain)
			if(dstFields.equalsIgnoreCase("ALL")){
				insertSql = DbEngine.makeSql(dstFieldList, dstTb)
			}
		}catch(Exception e){
			e.printStackTrace()
			logger.error(e.getMessage())
			return insertSql
		}
		return insertSql
	}

	

	public JdbcUtil getJdbcUtil() {
		return jdbcUtil;
	}
	public void setJdbcUtil(JdbcUtil jdbcUtil) {
		this.jdbcUtil = jdbcUtil;
	}
}
